package dao;

import domain.Product;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;

/**
 * 产品的dao，访问数据库完成数据增加、删除、修改（管理员）和浏览、查找（全员）
 */
public class ProductDao {
    public ArrayList<Product> browse(int pageIndex,String pname){
        /*
        用户分页浏览，含条件查找数据库内容
         */
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<Product> products = new ArrayList<>();
        Product product;
        int pageSize = 5;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM products ORDER BY pid LIMIT "+(pageIndex-1)*pageSize+","+pageSize;
            if (pname!=null)
                // 附条件查找
                sql = "SELECT * FROM products WHERE pname LIKE '%"+pname+"%'ORDER BY pid LIMIT "+(pageIndex-1)*pageSize+","+pageSize;
            rs = stmt.executeQuery(sql);
            while (rs.next()){
                product = new Product();
                product.setId(rs.getInt("pid"));
                product.setName(rs.getString("pname"));
                product.setCount(rs.getInt("count"));
                product.setPrice(rs.getDouble("price"));
                product.setImg(rs.getString("img"));
                product.setDescription(rs.getString("description"));
                products.add(product);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return products;
    }

    public int getTotalPage(String pname){
        /*
        返回信息总数（要分几页显示）
         */
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int totalPage = 1, pageSize = 5, pCount;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            String sql = "SELECT * FROM products";
            if (pname!=null)
                sql += " WHERE pname LIKE '%"+pname+"%'";
            rs = stmt.executeQuery(sql);
            rs.last();
            pCount = rs.getRow();
            totalPage = (int) Math.ceil((double) pCount/pageSize);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return totalPage;
    }

    public boolean exist(String pname){
        /*
        类似地，判断添加时输入的产品名是否已经存在
         */
        boolean exist = false;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM products WHERE pname='"+pname+"'";
            rs = stmt.executeQuery(sql);
            if (rs.next())
                exist = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return exist;
    }

    public boolean insert(Product product){
        /*
        添加产品的操作
         */
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "INSERT INTO products(pname,count,price,description) VALUES('"+
                    product.getName()+"','"+
                    product.getCount()+"','"+
                    product.getPrice()+"',";
            if (product.getDescription()==null)
                sql += "default)";
            else sql += "'"+product.getDescription()+"')";
            int num = stmt.executeUpdate(sql);
            return num > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(stmt, conn);
        }
        return false;
    }

    public Product search(int pid){
        /*
        根据id查找产品并返回
         */
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Product product;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM products WHERE pid='"+pid+"'";
            rs = stmt.executeQuery(sql);
            if (rs.next()){
                product = new Product();
                product.setId(rs.getInt("pid"));
                product.setName(rs.getString("pname"));
                product.setCount(rs.getInt("count"));
                product.setPrice(rs.getDouble("price"));
                product.setImg(rs.getString("img"));
                product.setDescription(rs.getString("description"));
                return product;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return null;
    }

    public boolean delete(int pid){
        /*
        删除产品的操作
         */
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "DELETE FROM products WHERE pid='"+pid+"'";
            int num = stmt.executeUpdate(sql);
            return num > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(stmt, conn);
        }
        return false;
    }

    public boolean modify(int pid,Product product){
        /*
        使用一个新对象来更新指定编号的产品
         */
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "UPDATE products SET pname='"+product.getName()+"',count='"+product.getCount()+"',price='"
                    +product.getPrice()+"',description='"+product.getDescription()+"' WHERE pid='"+pid+"'";
            int num = stmt.executeUpdate(sql);
            return num > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(stmt, conn);
        }
        return false;
    }

    public Product searchInMap(Map<Product,Integer> map, int pid){
        /*
        用于从Map对象里返回给定id的产品，以免search()方法创建新对象导致put()方法匹配不到
         */
        for (Product product : map.keySet()) {
            if (product.getId()==pid)return product;
        }
        return null;
    }

}
